package fr.upmc.cie.data.dbaccess;

import java.util.ArrayList;

import fr.upmc.cie.data.CategorieItem;
import fr.upmc.cie.data.ChannelItem;
import fr.upmc.cie.data.exception.CategorieNameDuplicateException;
import fr.upmc.cie.data.exception.CategorieNotExistException;
import fr.upmc.cie.data.exception.ChannelNotExistException;
import fr.upmc.cie.data.interfaces.ICategorieDataSource;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

public class DBCategorie implements ICategorieDataSource{

	private SQLiteStatement insertStmt;
	private static final String INSERT = "insert into categorie (name) values (?)";
	private SQLiteDatabase db;
	
	public DBCategorie(SQLiteDatabase db) {
		this.db = db;
	}

	@Override
	public void insert(CategorieItem ci) throws CategorieNameDuplicateException{
		this.insertStmt = db.compileStatement(INSERT);
		if (isCategorieNameExist(ci.getName())) throw new CategorieNameDuplicateException();
		insertStmt.bindString(1, ci.getName());
		ci.setId(insertStmt.executeInsert());
	}

	@Override
	public void delete(CategorieItem ci) throws CategorieNotExistException{
		if (!isCategorieExist(ci.getId())) throw new CategorieNotExistException();
		db.delete("categorie", "id_categorie=?", new String[]{String.valueOf(ci.getId())});
		db.delete("categorie_channel", "id_categorie=?", new String[]{String.valueOf(ci.getId())});
	}

	@Override
	public void update(CategorieItem ci) throws CategorieNameDuplicateException,CategorieNotExistException{
		if (isCategorieNameExist(ci.getName())&&getCategorieByName(ci.getName()).getId()!=ci.getId()) throw new CategorieNameDuplicateException();
		if (!isCategorieExist(ci.getId())) throw new CategorieNotExistException();
		db.update("categorie", ci.getContentValues(), "id_categorie=?", new String[]{String.valueOf(ci.getId())});
	}

	@Override
	public ArrayList<CategorieItem> getAllCategories(){
		DBChannel channel = new DBChannel(db);
		ArrayList<CategorieItem> result = new ArrayList<CategorieItem>();
		Cursor cursor = db.query("categorie", null, null, null, null, null, null);
		if (cursor.moveToFirst()){
			do{
				CategorieItem ci = new CategorieItem();
				ci.setId(cursor.getLong(0));
				ci.setName(cursor.getString(1));
				result.add(ci);
			}while(cursor.moveToNext());
			if (cursor != null && !cursor.isClosed()) {cursor.close();}
			for(CategorieItem ci : result){
				ArrayList<ChannelItem> cl = new ArrayList<ChannelItem>();
				cursor = db.query("categorie_channel", new String[]{"id_channel"}, 
						"id_categorie=?", new String[]{String.valueOf(ci.getId())}, 
						null, null, null);
				
				if (cursor.moveToFirst()){
					do{
						try {
							cl.add(channel.getChannelById(cursor.getLong(0)));
						} catch (ChannelNotExistException e) {
							e.printStackTrace();
						}
					}while(cursor.moveToNext());
					if (cursor != null && !cursor.isClosed()) {cursor.close();}
				}
				ci.setChannels(cl);
				if (cursor != null && !cursor.isClosed()) {cursor.close();}
			}
		}
		return result;
	}

	@Override
	public CategorieItem getCategorieById(Long id) throws CategorieNotExistException{
		DBChannel channel = new DBChannel(db);
		Cursor cursor = db.query("categorie", null, "id_categorie=?", new String[]{String.valueOf(id)}, null, null, null);
		if (cursor.moveToFirst()){
			CategorieItem ci = new CategorieItem();
			ci.setId(cursor.getLong(0));
			ci.setName(cursor.getString(1));
			if (cursor != null && !cursor.isClosed()) {cursor.close();}
			ArrayList<ChannelItem> cl = new ArrayList<ChannelItem>();
			Cursor cursors = db.query("categorie_channel", new String[]{"id_channel"}, 
					"id_categorie=?", new String[]{String.valueOf(ci.getId())}, 
					null, null, null);
			
			if (cursors.moveToFirst()){
				do{
					try {
						cl.add(channel.getChannelById(cursors.getLong(0)));
					} catch (ChannelNotExistException e) {
						e.printStackTrace();
					}
				}while(cursors.moveToNext());
				if (cursors != null && !cursors.isClosed()) {cursors.close();}
			}
			ci.setChannels(cl);
			return ci;
		}else{
			throw new CategorieNotExistException();
		}
	}

	@Override
	public CategorieItem getCategorieByName(String name) throws CategorieNotExistException{
		DBChannel channel = new DBChannel(db);
		Cursor cursor = db.query("categorie", null, "name=?", new String[]{String.valueOf(name)}, null, null, null);
		if (cursor.moveToFirst()){
			CategorieItem ci = new CategorieItem();
			ci.setId(cursor.getLong(0));
			ci.setName(cursor.getString(1));
			if (cursor != null && !cursor.isClosed()) {cursor.close();}
			ArrayList<ChannelItem> cl = new ArrayList<ChannelItem>();
			cursor = db.query("categorie_channel", new String[]{"id_channel"}, 
					"id_categorie=?", new String[]{String.valueOf(ci.getId())}, 
					null, null, null);
			
			if (cursor.moveToFirst()){
				do{
					try {
						cl.add(channel.getChannelById(cursor.getLong(0)));
					} catch (ChannelNotExistException e) {
						e.printStackTrace();
					}
				}while(cursor.moveToNext());
				if (cursor != null && !cursor.isClosed()) {cursor.close();}
			}
			ci.setChannels(cl);			
			return ci;
		}else{
			throw new CategorieNotExistException();
		}
	}
	

	/**
	 * Tester si le nom existe dans la base
	 * @param name
	 * @return
	 */
	private boolean isCategorieNameExist(String name){
		Cursor cursor = db.query("categorie", null, "name=?", new String[]{name}, null, null, null);
		if (!cursor.moveToFirst()){
			return false;
		}else{
			if (!cursor.isClosed()) cursor.close();
			return true;
		}
	}
	
	/**
	 * Tester si la categorie existe
	 * @param id
	 * @return
	 */
	private boolean isCategorieExist(Long id){
		Cursor cursor = db.query("categorie", null, "id_categorie=?", new String[]{String.valueOf(id)}, null, null, null);
		if (!cursor.moveToFirst()){
			return false;
		}else{
			if (!cursor.isClosed()) cursor.close();
			return true;
		}
	}

}
